package com.yixun.qfbao.util;

import com.yixun.qfbao.model.AgentRankReport;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class Export {
	private static Logger logger= LoggerFactory.getLogger(Export.class);

	/**
	 * 导出到excel
	 */
	public static void exportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
								   HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				HSSFCellStyle headStyle = wb.createCellStyle();
				HSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				HSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]+"_"+j))?"":map.get(header[i]+"_"+j));
					}
				}
			}

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String mimetype = "application/x-msdownload";
			String downFileName = df.format(new Date()) + ".xls";
			String inlineType = "attachment";
			response.setHeader("Content-Disposition", inlineType
					+ ";filename=\"" + downFileName + "\"");
			OutputStream out = response.getOutputStream();
			response.setContentType(mimetype);
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportExcel error: {}", e);
		}
	}
	/**
	 * 导出到excel
	 */
	public static void exportReportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
										 HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				XSSFCellStyle headStyle = wb.createCellStyle();
				XSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				XSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				XSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					XSSFRichTextString text = new XSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String mimetype = "application/x-msdownload";
			String downFileName = df.format(new Date()) + ".xlsx";
			String inlineType = "attachment";
			response.setHeader("Content-Disposition", inlineType
					+ ";filename=\"" + downFileName + "\"");
			OutputStream out = response.getOutputStream();
			response.setContentType(mimetype);
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportReportExcel error: {}", e);
		}
	}
	/**
	 * 导出报备到excel
	 */
	public static void exportCustomerReportExcel(String fileName,List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
												 HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				XSSFCellStyle headStyle = wb.createCellStyle();
				XSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				XSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				XSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					XSSFRichTextString text = new XSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String mimetype = "application/x-msdownload";
			String downFileName =fileName+"-"+ df.format(new Date()) + ".xlsx";
			String inlineType = "attachment";
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", inlineType
					+ ";filename=\"" +URLEncoder.encode(downFileName, "utf-8")+ "\"");
			OutputStream out = response.getOutputStream();
			response.setContentType(mimetype);
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportCustomerReportExcel error: {}", e);
		}
	}

	/**
	 * 导出佣金设置详情到excel
	 */
	public static void exportCommisseionSetDetailExcel(String fileName,List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,
													   HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			XSSFWorkbook wb = new XSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				XSSFSheet sheet = wb.createSheet();
				wb.setSheetName(n, name[n]);
				XSSFCellStyle headStyle = wb.createCellStyle();
				XSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 12);// 字号
				f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);

				sheet.setColumnWidth(0,20*100);
				sheet.setColumnWidth(1,70*100);
				sheet.setColumnWidth(2,60*100);
				sheet.setColumnWidth(3,70*100);
				sheet.setColumnWidth(4,110*100);
				sheet.setColumnWidth(5,60*100);
				sheet.setColumnWidth(6,60*100);
				sheet.setColumnWidth(7,40*100);

				XSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				XSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				XSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					XSSFRichTextString text = new XSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);
					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i,1);
						cell.setCellValue(StringUtils.isBlank(map.get(header[i]))?"":map.get(header[i]));
					}
				}
			}

			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String mimetype = "application/x-msdownload";
			String downFileName =fileName+"-"+ df.format(new Date()) + ".xlsx";
			String inlineType = "attachment";
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", inlineType
					+ ";filename=\"" +URLEncoder.encode(downFileName, "utf-8")+ "\"");
			OutputStream out = response.getOutputStream();
			response.setContentType(mimetype);
			wb.write(out);
		} catch (Exception e) {
			logger.error("exportCommisseionSetDetailExcel error: {}", e);
		}
	}

	/**
	 * 导出到excel
	 */
	public static void exportAgentExcel(List<AgentRankReport> list, HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			if(null != list && list.size() > 0){
				HSSFSheet sheet = wb.createSheet("经纪人排行表");
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(XSSFCellStyle.ALIGN_CENTER);

				String[] header = new String[]{"TOP10排行榜","经纪人","客户数","报备客户","报备成功客户","到访客户","认筹客户","认购客户","签约客户"};
				HSSFRow row = sheet.createRow((int) 0);

				Font font = wb.createFont();
//				font.setBold(false);
				font.setFontName("仿宋_GB2312");
				font.setFontHeightInPoints((short) 12);
				style.setFont(font);
				for (int i = 0;i < header.length;i++) {
					sheet.setColumnWidth(i, 3766);
					if(i==0){
						CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 0);
						sheet.addMergedRegion(cra);
					}
					if(i==1){
						CellRangeAddress cra = new CellRangeAddress(0, 1, 1, 1);
						sheet.addMergedRegion(cra);
					}
					if(i==2){
						CellRangeAddress cra = new CellRangeAddress(0, 0, 2, 7);
						sheet.addMergedRegion(cra);
					}
					if(i==3){
						row = sheet.createRow((int) 1);
					}
					HSSFCell cell = null;
					int cellNum = i;
					if(i>=3){
						cell = row.createCell(cellNum-1);
					}else{
						cell = row.createCell(i);
					}
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}
				//数据体基本样式
				HSSFCell cell = null;
				for(int ii=0;ii<list.size();ii++){
					AgentRankReport arr = list.get(ii);
					row = sheet.createRow(ii+2);
					for(int jj=0;jj<8;jj++){
						if(jj==0){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							cell.setCellValue("NO."+(ii+1));
						}else if(jj==1){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(arr.getAccountName());
							cell.setCellValue(text);
						}else if(jj==2){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankOne()));
							cell.setCellValue(text);
						}else if(jj==3){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankTwo()));
							cell.setCellValue(text);
						}else if(jj==4){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankThree()));
							cell.setCellValue(text);
						}else if(jj==5){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankFour()));
							cell.setCellValue(text);
						}else if(jj==6){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankFive()));
							cell.setCellValue(text);
						}else if(jj==7){
							cell = row.createCell(jj);
							cell.setCellStyle(style);
							HSSFRichTextString text = new HSSFRichTextString(String.valueOf(arr.getAgentRankSix()));
							cell.setCellValue(text);
						}
					}
				}
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				String mimetype = "application/x-msdownload";
				String downFileName = "agent-"+df.format(new Date())+".xls";
				String inlineType = "attachment";
				response.setHeader("Content-Disposition", inlineType  + ";filename=\"" + downFileName + "\"");
				OutputStream out = response.getOutputStream();
				response.setContentType(mimetype);
				wb.write(out);
			}
		} catch (Exception e) {
			logger.error("exportAgentExcel error: {}", e);
		}
	}
}
